package model

import (
	"fmt"
	"github.com/zeromicro/go-zero/core/logx"
	"github.com/zeromicro/go-zero/core/stores/sqlx"
	"golang.org/x/crypto/bcrypt"
	"strings"
)

func CreateTables(sqlConn sqlx.SqlConn, dbType string) error {
	var createAgentHeartbeatsTableSQL, createDeploymentLogsTableSQL, createComponentInfoTableSQL, createUserInfoTableSQL, createRolesTableSQL, createDocumentationTableSQL, createComponentConfigsTableSQL, createClusterInfoTableSQL, createAuditLogsTableSQL string

	switch strings.ToLower(dbType) {
	case "mysql":
		createAgentHeartbeatsTableSQL = `
        CREATE TABLE IF NOT EXISTS yunfan_bigdata.agent_heartbeats (
            id INTEGER NOT NULL AUTO_INCREMENT COMMENT '自增主键，用于唯一标识每条记录',
            cluster_id varchar(100) NOT NULL COMMENT '集群id，用于区分不同集群',
            agent_id varchar(255) NOT NULL COMMENT 'agent的唯一标识符，通常是一个UUID或其他唯一字符串',
            hostname varchar(255) NOT NULL COMMENT 'agent的主机名',
            ip_address varchar(50) NOT NULL COMMENT 'agent的IP地址',
            status varchar(50) NOT NULL COMMENT 'agent的状态，例如“alive”、“dead”等',
            cpu_usage float NOT NULL COMMENT 'CPU使用率，使用FLOAT类型存储',
            memory_usage float NOT NULL COMMENT '内存使用量，使用FLOAT类型存储',
            disk_space float NOT NULL COMMENT '磁盘空间使用量，使用FLOAT类型存储',
            last_heartbeat TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) COMMENT '最后一次收到心跳的时间戳',
            process_count int COMMENT '当前进程数',
            PRIMARY KEY (id),
            UNIQUE KEY unique_hostname (cluster_id, agent_id, ip_address)
        ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用于存储agent心跳信息的表';`

		createDeploymentLogsTableSQL = `
        CREATE TABLE IF NOT EXISTS yunfan_bigdata.deployment_logs (
            cluster_id varchar(100) NOT NULL COMMENT '集群id，用于区分不同集群',
            task_id VARCHAR(100) NOT NULL COMMENT '任务的唯一标识符',
            node_host VARCHAR(100) NOT NULL COMMENT '节点的主机地址',
            component VARCHAR(100) NOT NULL COMMENT '组件名称',
            component_role varchar(100) NOT NULL COMMENT '角色类型',
            step_number INT NOT NULL COMMENT '步骤编号',
            message TEXT NOT NULL COMMENT '步骤描述',
            log_level VARCHAR(10) NOT NULL COMMENT '日志级别',
            created_at TIMESTAMP(3) NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '记录创建的时间戳',
            UNIQUE KEY unique_deployment (cluster_id, task_id, node_host, component, component_role, step_number)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='部署进度信息表';`

		createComponentInfoTableSQL = `
        CREATE TABLE IF NOT EXISTS yunfan_bigdata.component_info (
            id INTEGER NOT NULL AUTO_INCREMENT COMMENT '自增的唯一标识符',
            cluster_id varchar(100) NOT NULL COMMENT '集群id，用于区分不同集群',
            component_name varchar(100) NOT NULL COMMENT '组件名称',
            version varchar(100) NOT NULL COMMENT '组件的版本信息',
            node_host varchar(100) NOT NULL COMMENT '主机名',
            node_ip varchar(100) NOT NULL COMMENT '主机ip',
            component_role varchar(100) DEFAULT NULL COMMENT '组件的角色',
            home_path varchar(255) NOT NULL COMMENT '组件的 home 目录路径',
            data_storage_dir varchar(255) DEFAULT NULL COMMENT '组件的数据存储目录路径',
            port varchar(100) NOT NULL COMMENT '组件使用的端口号',
            status tinyint(1) DEFAULT '0' COMMENT '组件状态',
            additional_info text COMMENT '其他附加信息',
            created_at TIMESTAMP(3) NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '记录创建时间',
            updated_at TIMESTAMP(3) NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) COMMENT '记录最后更新时间',
            PRIMARY KEY (id),
            UNIQUE KEY unique_component_info (cluster_id, component_name, version, node_ip, port)
       ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用于记录大数据组件部署信息的表';`

		createUserInfoTableSQL = `
        CREATE TABLE IF NOT EXISTS yunfan_bigdata.user_info (
            id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '用户唯一标识符',
            username VARCHAR(50) NOT NULL COMMENT '用户名',
            password VARCHAR(255) NOT NULL COMMENT '用户密码',
            role ENUM('SuperAdmin','OpsAdmin','DevAdmin','Auditor','User') NOT NULL DEFAULT 'User' COMMENT '用户角色',
            email VARCHAR(100) NOT NULL COMMENT '用户邮箱',
            department VARCHAR(100) NOT NULL COMMENT '用户所属部门',
            created_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '记录创建时间',
            updated_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) COMMENT '记录最后更新时间',
            deleted_at TIMESTAMP(3) NULL DEFAULT NULL COMMENT '删除时间',
            PRIMARY KEY (id),
            UNIQUE KEY username (username)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用户信息表';`

		createRolesTableSQL = `
        CREATE TABLE IF NOT EXISTS yunfan_bigdata.roles (
            role_id INTEGER NOT NULL AUTO_INCREMENT COMMENT '角色唯一标识',
            role_name VARCHAR(255) NOT NULL COMMENT '角色名称',
            description TEXT COMMENT '角色描述',
            permissions TEXT COMMENT '角色权限',
            created_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '创建时间',
            updated_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) COMMENT '更新时间',
            deleted_at TIMESTAMP NULL DEFAULT NULL COMMENT '删除时间，逻辑删除',
            PRIMARY KEY (role_id),
            UNIQUE KEY role_name (role_name)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='角色信息表';`

		createDocumentationTableSQL = `
        CREATE TABLE IF NOT EXISTS yunfan_bigdata.documentation (
            id INTEGER NOT NULL AUTO_INCREMENT COMMENT '主键，唯一标识每一条文档记录',
            title varchar(255) NOT NULL COMMENT '文档标题',
            author varchar(255) NOT NULL COMMENT '文档作者',
            content longtext NOT NULL COMMENT '文档内容，使用LONGTEXT以存储更长的文本',
            component varchar(50) NOT NULL COMMENT '文档所属组件（如 Kafka、Hadoop 等）',
            tags json DEFAULT NULL COMMENT '文档标签，使用JSON格式存储多个标签',
            content_summary varchar(255) GENERATED ALWAYS AS (left(content,100)) VIRTUAL COMMENT '文档内容的摘要信息，自动生成前100个字符',
            created_at TIMESTAMP(3) NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '文档创建时间',
            updated_at TIMESTAMP(3) NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) COMMENT '文档更新时间',
            PRIMARY KEY (id),
            KEY idx_component (component) COMMENT '为 component 字段添加普通索引',
            KEY idx_author (author) COMMENT '为 author 字段添加普通索引',
            KEY idx_component_created_at (component,created_at) COMMENT '为 component 和 created_at 字段添加组合索引',
            FULLTEXT KEY title (title,content) COMMENT '为标题和内容添加全文索引，优化全文搜索性能'
        ) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='存储文档数据的表';`

		createComponentConfigsTableSQL = `
        CREATE TABLE IF NOT EXISTS yunfan_bigdata.component_configs (
            id int NOT NULL AUTO_INCREMENT COMMENT '主键，自增ID',
            component_name varchar(255) COLLATE utf8mb4_general_ci NOT NULL COMMENT '组件名称，例如：zookeeper，hdfs等',
            config_name varchar(255) COLLATE utf8mb4_general_ci NOT NULL COMMENT '配置项名称，例如：tickTime，dataDir等',
            config_value varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '配置项的当前值',
            default_value text COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '配置项的默认值',
            version int NOT NULL COMMENT '配置项的版本号，用于版本控制',
            config_file varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '配置项所在的配置文件名称',
            description text COLLATE utf8mb4_general_ci COMMENT '配置项的详细描述',
            created_at TIMESTAMP(3) NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '记录创建时间',
            updated_at TIMESTAMP(3) NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) COMMENT '记录更新时间',
            PRIMARY KEY (id),
            UNIQUE KEY unique_component_config (component_name,config_name,version,config_file),
            INDEX idx_component_name_config_name (component_name, config_name)
       ) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='存储组件配置项及其版本信息';`

		createClusterInfoTableSQL = fmt.Sprintf(`
		CREATE TABLE IF NOT EXISTS yunfan_bigdata.cluster_info (
		    id int NOT NULL AUTO_INCREMENT COMMENT '自增主键，用于唯一标识每一条记录',
		    cluster_id varchar(255) COLLATE utf8mb4_general_ci NOT NULL COMMENT '集群的唯一标识符',
		    cluster_name varchar(255) COLLATE utf8mb4_general_ci NOT NULL COMMENT '集群名称',
		    node_count int NOT NULL COMMENT '集群中的节点数量',
		    cluster_version varchar(255) COLLATE utf8mb4_general_ci NOT NULL COMMENT '集群版本',
		    created_at TIMESTAMP(3) NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '集群创建时间',
		    updated_at TIMESTAMP(3) NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) COMMENT '集群信息更新时间',
		    status varchar(50) COLLATE utf8mb4_general_ci NOT NULL COMMENT '集群状态（如：active, inactive, deleting）',
		    description text COLLATE utf8mb4_general_ci COMMENT '集群描述',
		    owner varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '集群所有者',
		    region varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '集群所在的地理区域',
		    PRIMARY KEY (id),
		    UNIQUE KEY cluster_id_unique (cluster_id)
		) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='集群信息元数据表';`)

		createAuditLogsTableSQL = fmt.Sprintf(`
		CREATE TABLE IF NOT EXISTS yunfan_bigdata.audit_logs (
		    id int NOT NULL AUTO_INCREMENT COMMENT '自增ID',
		    client_ip varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '客户端IP地址',
		    user_id varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '用户ID',
		    username varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '用户名',
		    operation_type varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '操作类型（HTTP方法）',
		    request_uri text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '请求的URI',
		    request_body text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT '请求体（可选）',
		    response_body text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT '响应体（可选）',
		    status_code int NOT NULL COMMENT '操作结果状态码',
		    user_agent varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '客户端用户代理',
		    server_node varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '处理请求的服务器节点',
		    start_time timestamp(3) NOT NULL COMMENT '请求开始时间',
		    end_time timestamp(3) NOT NULL COMMENT '请求结束时间',
		    duration bigint NOT NULL COMMENT '请求持续时间（毫秒）',
		    PRIMARY KEY (id)
		) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='审计日志表';`)

		createDatabase := "CREATE DATABASE IF NOT EXISTS yunfan_bigdata DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_general_ci;"
		// 创建数据库
		_, err := sqlConn.Exec(createDatabase)
		if err != nil {
			logx.Errorf("failed to create yunfan_bigdata database: %v", err)
			return fmt.Errorf("failed to create yunfan_bigdata database: %v", err)
		}

	case "sqlite":
		createAgentHeartbeatsTableSQL = `
        CREATE TABLE IF NOT EXISTS agent_heartbeats (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            agent_id TEXT NOT NULL,
            cluster_id TEXT NOT NULL,
            hostname TEXT NOT NULL,
            ip_address TEXT NOT NULL,
            status TEXT NOT NULL,
            cpu_usage REAL NOT NULL,
            memory_usage REAL NOT NULL,
            disk_space REAL NOT NULL,
            last_heartbeat TEXT NOT NULL DEFAULT (datetime('now', 'localtime')),
            process_count INTEGER,
            UNIQUE(cluster_id, agent_id, ip_address)
        );`

		createDeploymentLogsTableSQL = `
        CREATE TABLE IF NOT EXISTS deployment_logs (
            cluster_id TEXT NOT NULL,
            task_id TEXT NOT NULL,
            node_host TEXT NOT NULL,
            component TEXT NOT NULL,
            component_role TEXT NOT NULL,
            step_number INTEGER NOT NULL,
            message TEXT NOT NULL,
            log_level TEXT NOT NULL,
            created_at TEXT NOT NULL DEFAULT (datetime('now', 'localtime')),
            UNIQUE(cluster_id, task_id, node_host, component, component_role, step_number)
        );`

		createComponentInfoTableSQL = `
        CREATE TABLE IF NOT EXISTS component_info (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            cluster_id TEXT NOT NULL,
            component_name TEXT NOT NULL,
            version TEXT NOT NULL,
            node_host TEXT NOT NULL,
            node_ip TEXT NOT NULL,
            component_role TEXT,
            home_path TEXT NOT NULL,
            data_storage_dir TEXT,
            port TEXT NOT NULL,
            status INTEGER DEFAULT 0,
            additional_info TEXT,
            created_at TEXT NOT NULL DEFAULT (datetime('now', 'localtime')),
            updated_at TEXT NOT NULL DEFAULT (datetime('now', 'localtime')),
            UNIQUE(cluster_id, component_name, version, node_ip, port)
        );`

		createUserInfoTableSQL = `
        CREATE TABLE IF NOT EXISTS user_info (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            username VARCHAR(64) NOT NULL,
            password VARCHAR(64) NOT NULL,
            role VARCHAR(64) NOT NULL DEFAULT 'User',
            email VARCHAR(100) NOT NULL,
            department VARCHAR(64) NOT NULL,
            created_at TEXT NOT NULL DEFAULT (datetime('now', 'localtime')),
            updated_at TEXT NOT NULL DEFAULT (datetime('now', 'localtime')),
            deleted_at TEXT
        );`

		createRolesTableSQL = `
        CREATE TABLE IF NOT EXISTS roles (
            role_id INTEGER PRIMARY KEY AUTOINCREMENT,
            role_name TEXT NOT NULL,
            description TEXT,
            permissions TEXT,
            created_at TEXT NOT NULL DEFAULT (datetime('now', 'localtime')),
            updated_at TEXT NOT NULL DEFAULT (datetime('now', 'localtime')),
            deleted_at TEXT,
            UNIQUE(role_name)
        );`

		createDocumentationTableSQL = `
        CREATE TABLE IF NOT EXISTS documentation (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            title TEXT NOT NULL,
            author TEXT NOT NULL,
            content TEXT NOT NULL,
            component TEXT NOT NULL,
            tags TEXT,
            content_summary TEXT,
            created_at TEXT NOT NULL DEFAULT (datetime('now', 'localtime')),
            updated_at TEXT NOT NULL DEFAULT (datetime('now', 'localtime'))
        );`

		createComponentConfigsTableSQL = `
        CREATE TABLE IF NOT EXISTS component_configs (
            id INTEGER PRIMARY KEY AUTOINCREMENT, -- 主键，自增ID
            component_name TEXT NOT NULL,         -- 组件名称，例如：zookeeper，hdfs等
            config_name TEXT NOT NULL,            -- 配置项名称，例如：tickTime，dataDir等
            config_value TEXT DEFAULT NULL,       -- 配置项的当前值
            default_value TEXT DEFAULT NULL,      -- 配置项的默认值
            version INTEGER NOT NULL,             -- 配置项的版本号，用于版本控制
            config_file TEXT,                     -- 配置项所在的配置文件名称
            description TEXT,                     -- 配置项的详细描述
            created_at TEXT NOT NULL DEFAULT (datetime('now', 'localtime')), -- 记录创建时间
            updated_at TEXT NOT NULL DEFAULT (datetime('now', 'localtime')), -- 记录更新时间
            UNIQUE(component_name, config_name, version, config_file));`

		createClusterInfoTableSQL = `
		CREATE TABLE IF NOT EXISTS cluster_info (
			id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, -- SQLite使用AUTOINCREMENT
			cluster_id TEXT NOT NULL, -- 使用TEXT代替VARCHAR
			cluster_name TEXT NOT NULL, -- 使用TEXT代替VARCHAR
			node_count INTEGER NOT NULL, -- 使用INTEGER代替INT
			cluster_version TEXT NOT NULL, -- 使用TEXT代替VARCHAR
			created_at TEXT NOT NULL DEFAULT (datetime('now', 'localtime')),
            updated_at TEXT NOT NULL DEFAULT (datetime('now', 'localtime')),
			status TEXT NOT NULL, -- 使用TEXT代替VARCHAR
			description TEXT, -- 使用TEXT代替TEXT
			owner TEXT DEFAULT NULL, -- 使用TEXT代替VARCHAR
			region TEXT DEFAULT NULL, -- 使用TEXT代替VARCHAR
			UNIQUE(cluster_id) -- UNIQUE约束
		);`

		createAuditLogsTableSQL = `
		CREATE TABLE IF NOT EXISTS audit_logs (
		    id INTEGER PRIMARY KEY AUTOINCREMENT,
		    client_ip TEXT NOT NULL,         -- 客户端IP地址
		    user_id TEXT,                    -- 用户ID
		    username TEXT,                   -- 用户名
		    operation_type TEXT NOT NULL,    -- 操作类型（HTTP方法）
		    request_uri TEXT NOT NULL,       -- 请求的URI
		    request_body TEXT,               -- 请求体（可选）
		    response_body TEXT,              -- 响应体（可选）
		    status_code INTEGER NOT NULL,    -- 操作结果状态码
		    user_agent TEXT,                 -- 客户端用户代理
		    server_node TEXT,                -- 处理请求的服务器节点
		    start_time TEXT NOT NULL,        -- 请求开始时间
		    end_time TEXT NOT NULL,          -- 请求结束时间
		    duration INTEGER NOT NULL        -- 请求持续时间（毫秒）
		);`

	default:
		return fmt.Errorf("unsupported database type: %s", dbType)
	}

	// 创建表
	tables := []string{
		createAgentHeartbeatsTableSQL,
		createDeploymentLogsTableSQL,
		createComponentInfoTableSQL,
		createUserInfoTableSQL,
		createRolesTableSQL,
		createDocumentationTableSQL,
		createComponentConfigsTableSQL,
		createClusterInfoTableSQL,
		createAuditLogsTableSQL,
	}

	logx.Infof("元数据表初始化...")
	for _, tableSQL := range tables {
		_, err := sqlConn.Exec(tableSQL)
		if err != nil {
			logx.Errorf("failed to create table: %v", err)
			return fmt.Errorf("failed to create table: %w", err)
		}
	}

	return nil
}

func InitializeAdminUser(sqlConn sqlx.SqlConn, dbType string) error {
	var countQuery, insertQuery string

	switch strings.ToLower(dbType) {
	case "mysql":
		countQuery = "SELECT COUNT(*) FROM yunfan_bigdata.user_info WHERE username = ?"
		insertQuery = "INSERT INTO yunfan_bigdata.user_info (username, password, role, email, department) VALUES (?, ?, ?, ?, ?)"
	case "sqlite":
		countQuery = "SELECT COUNT(*) FROM user_info WHERE username = ?"
		insertQuery = "INSERT INTO user_info (username, password, role, email, department) VALUES (?, ?, ?, ?, ?)"
	default:
		return fmt.Errorf("unsupported database type: %s", dbType)
	}

	// 检查管理员用户是否存在
	var count int
	err := sqlConn.QueryRow(&count, countQuery, "admin")
	if err != nil {
		logx.Errorf("failed to check admin user existence: %v", err)
		return fmt.Errorf("failed to check admin user existence: %w", err)
	}

	// 如果不存在，插入管理员用户
	if count == 0 {
		hashedPassword, err := bcrypt.GenerateFromPassword([]byte("admin"), bcrypt.DefaultCost)
		if err != nil {
			logx.Errorf("failed to hash password: %v", err)
			return fmt.Errorf("failed to hash password: %w", err)
		}

		// 插入用户信息到数据库
		_, err = sqlConn.Exec(insertQuery, "admin", string(hashedPassword), "SuperAdmin", "", "")
		if err != nil {
			logx.Errorf("admin user created failed: %v", err)
			return fmt.Errorf("admin user created failed: %w", err)
		}
		logx.Infof("admin user created successfully")
	}

	return nil
}
